﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace sjzyoukucaiji
{
    public class AccessDb
    {
        private string myConnString = ConfigurationManager.ConnectionStrings["sjzyoukucaiji"].ConnectionString;
        private OleDbConnection _conn;
        public OleDbConnection conn
        {
            get
            {
                if (_conn == null)
                    _conn = new OleDbConnection(myConnString);
                return _conn;
            }
        }
        private OleDbCommand _comm;
        public OleDbCommand comm
        {
            get
            {
                if (_comm == null)
                {
                    _comm = new OleDbCommand();
                    _comm.Connection = conn;
                }
                return _comm;
            }
        }
        private void Openconn()
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
        }
        private void Closeconn()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        /// <summary> 
        /// 执行Sql查询语句 (增删改)
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        public int ExecuteSql(string sqlstr)
        {
            try
            {
                Openconn();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                return comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                Closeconn();
            }
        }
        /// <summary> 
        /// 返回指定Sql语句的DataTable 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>DataTable</returns> 
        public DataTable GetdataTableBySql(string sqlstr)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable datatable = new DataTable();
            try
            {
                Openconn();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(datatable);
            }
            catch (Exception e)
            {
                Err.writeInLog(e.Message);
            }
            finally
            {
                Closeconn();
            }
            return datatable;
        }
        public int ExecSqlAndReturnID(string strSQL, string[] strParams, object[] strValues)
        {
            Openconn();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = this.conn;
           
            if ((strParams != null) && (strParams.Length != strValues.Length))
                throw new Exception("查询参数和值不对应!");
            cmd.CommandText = strSQL;
            cmd.CommandType = CommandType.Text;
            if (strParams != null)
            {
                for (int i = 0; i < strParams.Length; i++)
                {
                    OleDbParameter param = new OleDbParameter(strParams[i], strValues[i]);
                    cmd.Parameters.Add(param);

                }
            }
            int isUpdateOk = -1;
            isUpdateOk = Convert.ToInt32(cmd.ExecuteNonQuery());
            if (isUpdateOk > 0)
            {
                cmd.Parameters.Clear();
                cmd.CommandText = "Select @@identity as [MaxID]";
                int ret = int.Parse(cmd.ExecuteScalar().ToString());
                Closeconn();
                return ret;
            }
            Closeconn();
            return 0;
        }
        public bool ExecuteUpdate(string strSql, string[] strParams, object[] strValues)
        {
            Openconn();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = this.conn;
        
            if ((strParams != null) && (strParams.Length != strValues.Length))
                throw new Exception("查询参数和值不对应!");
            cmd.CommandText = strSql;
            cmd.CommandType = CommandType.Text;
            if (strParams != null)
            {
                for (int i = 0; i < strParams.Length; i++)
                {
                    OleDbParameter param = new OleDbParameter(strParams[i], strValues[i]);
                    cmd.Parameters.Add(param);
                }
            }
            bool ret = cmd.ExecuteNonQuery() > 0;
            Closeconn();
            return ret;
        }
    }
}
